from urllib.parse import quote_plus

import pandas as pd
from sqlalchemy import create_engine

username = 'bj_readonly'
password = 'Bj_readonly123#@!'
hostname = '10.90.12.206'
port = '3306'
database = 'parts_retail_mall_online'

encoded_password = quote_plus(password)
connection_string = f"mysql+pymysql://{username}:{encoded_password}@{hostname}:{port}/{database}"
engine = create_engine(connection_string)
# 读取 MySQL 表数据
query = "SELECT id, store_id, shop_id, sale_out_id, sale_out_code, receipt_bill_id, receipt_bill_item_id, channel, terminal_sn, client_sn, subject, payway, operator, payer_uid, total_amount, status, order_status, finish_time, channel_finish_time, created_time, del_flag, customer_id, scene, child_sale_out_id, child_sale_out_code, ip, borrow_Id, trade_no, shipping_status FROM parts_pay_sqb_trade_record where order_status='PAID' and created_time>'2025-01-01' "

df = pd.read_sql(query, engine)

total_amount=df['total_amount'].sum()
hy_amount=total_amount * 0.008
max_value = df['total_amount'].max()
min_value = df['total_amount'].min()
fw90 = df['total_amount'].quantile(0.9)
mean_value = df['total_amount'].mean()

print("max_value:",max_value,"min_value:",min_value,"fw90:",fw90,"mean_value:",mean_value)
print("total_amount:",total_amount,"hy_amount:",hy_amount)


# 显示前 5 行数据
print("前 5 行数据：")
print(df.head())

print("shape",df.shape[1])


c = df[['store_id','total_amount']].groupby(['store_id'],as_index=False).agg({'total_amount':'sum'})
c.sort_values(['total_amount'],ascending=False,inplace=True)
print(c)

o = df[['store_id','sale_out_code']].groupby(['store_id'],as_index=False).agg({'sale_out_code':'count'})

print(o.head())